if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].mTL_UpdateFileAttach') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].mTL_UpdateFileAttach
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].mTL_DeleteFileAttach') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].mTL_DeleteFileAttach
GO
/*
Nguoi tao: PHUONGTT
Ngay tao: 27/08/2012
Chuc nang: cap nhat file dinh kem vao database
Tham so:	@p_recordid:	ID cua ho so
			@p_listfile:	danh sach file dinh kem	
			@p_table:		bang lien quan
			@colIdTable:	Ten cot luu id cua bang @p_table
*/
CREATE PROCEDURE dbo.mTL_UpdateFileAttach
	@p_recordid		varchar(50)
	,@p_listfile	Nvarchar(max)
	,@p_doctype		Nvarchar(50)
	,@p_table		varchar(100)
	,@colIdTable	Varchar(100)
	,@sDelimitor	varchar(10) = '!~~!'
AS
	Declare @chkID varchar(100),@file varchar(500),@close varchar(300)	
	,@strSql Varchar(max)
	BEGIN TRANSACTION	
		If @p_listfile is not null and @p_listfile <> '' 
			Begin
				Declare @tempFile Table (P_ID int IDENTITY (1,1),C_FILE_NAME	Varchar(500))				
				Insert Into @tempFile
				Select [part] From [dbo].[f_ConvertStringToTable](@p_listfile,@sDelimitor)
			End		
		If (@p_doctype is not null and @p_doctype <> '') AND (@p_listfile is not null and @p_listfile <> '' )
			Begin
				Delete T_EFYLIB_FILE 
				where	FK_DOC = @p_recordid 
						and C_DOC_TYPE = @p_doctype
				Insert Into T_EFYLIB_FILE ( FK_DOC, C_TABLE_OBJECT, C_FILE_NAME, C_DOC_TYPE)	--cap nat file moi
				Select  @p_recordid, @p_table, A.C_FILE_NAME, @p_doctype
				From @tempFile A				
			End
		Set @strSql = 'Update ' + @p_table + ' Set C_FILENAME_LIST = ''' + @p_listfile + ''' Where ' + @colIdTable + ' = ''' + @p_recordid + ''''				
		Exec(@strSql)
	COMMIT TRANSACTION
	Return 0
GO
/*
Exec QLHT_UpdateFileAttach '5E445842-248A-432B-B000-8C5CD41B6AD2','DM_TLKT_01.113732:2012_08_27_0451000000518471!~!classRecord.js!~~!DM_TLKT_01.113756:2012_08_27_0451000000989762!~!banner-lleft.jpg','T_QLHT_RECORD','!~~!'
*/

/*
Nguoi tao: Truongdv
Ngay tao: 30/08/2012
Y nghia: SP xoa file
*/
CREATE PROCEDURE [dbo].mTL_DeleteFileAttach
	@fkdoc			Varchar(50)		-- id ho so
	,@filename		Varchar(500)	-- filename
	,@doctype		Varchar(30)	-- filename
	,@tableName		Varchar(100)	-- Ten bang
	,@colIdName		Varchar(100)	-- Ten khoa chinh cua bang
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		Begin
			Declare @fileNameList Nvarchar(4000)
					,@str Varchar(max)
			Set @fileNameList = ''
			SET XACT_ABORT ON -- Dat che do tu dong Rollback neu co loi xay ra
			BEGIN TRANSACTION
				If @fkdoc is not null And @fkdoc <> ''
					Begin
						Delete T_EFYLIB_FILE Where FK_DOC = @fkdoc And C_FILE_NAME = @filename And C_DOC_TYPE = @doctype
						Select @fileNameList = @fileNameList + C_FILE_NAME + '!~~!'
						From T_EFYLIB_FILE 
						Where FK_DOC = @fkdoc And C_DOC_TYPE = @doctype And C_TABLE_OBJECT = @tableName
						If @fileNameList <> ''
							Set @fileNameList = substring(@fileNameList, 1, len(@fileNameList) - 4)
						Set @str = 'Update ' + @tableName + ' Set C_FILENAME_LIST = ''' + @fileNameList + ''' Where ' + @colIdName + ' = ''' + @fkdoc + ''''
						Exec(@str)
					End
			COMMIT TRANSACTION
		End		
	SET NOCOUNT OFF
	Return 0
GO